IF OBJECT_ID('dbo.RptAgreeFileFolderLabels') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptAgreeFileFolderLabels
    IF OBJECT_ID('dbo.RptAgreeFileFolderLabels') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptAgreeFileFolderLabels >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptAgreeFileFolderLabels >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptAgreeFileFolderLabels
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 5/31/2007
--
-- Description: Source of Report, "AgreementFileFolderLabels.rdl"
--              In Insight DB Reporting Services
--
-- Parameters:  
--               @StartDate                  
--               @EndDate                    
--               @DateType              -- Created,Deadline,ALL         
--               @Institution                
--               @FolderNumber               
--               @FolderNumberEnd            
--               @PrincipalInvestigator      
--               @DomainUserId               
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptAgreeFileFolderLabels( )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 5/31/07     KC        Created
-- 7/9/07      KC        Changed Naming Conventions. Finalized Rpt.
-- 7/23/07     KC        Fact table now has PropNumber on it.
-- 7/31/07     KC        Fixed BudgetDate selects...
-- 8/1/07      KC        Added @PrincipalInvestigator as Search Criteria
-- 8/6/07      KC        Changed @DateType to INT for Frontend Control
-- 8/8/07      KC        Made Security Inner Join
-- 8/17/07     KC        Security changed to Cached table
-- 8/20/07     KC        Changed dates to look at InfoEdProposal.created_dt
-- 8/24/07     KC        Finalized for QA. Left BudgetYear check to rid
--                       resultset of duplicates. Don't need real budgets.
-- 8/27/07     KC        Change ProjectDates to Agreement table
-- 8/28/07     KC        Removed SvcEntityId from JOIN on Agreement Table.
--                       Added check for PROP.inst_code = 'PARTNERS'
--                       and PROP.submt_dt
--             KC        Added @StartOnLabel, changed Date to Optional.
--                       Bug with DateType fixed...
-- 8/29/07     KC        Added @FundNumber as a Param
-- 9/10/07     KC        Changed to new source tables
-- 9/20/07     KC        Fixed SponsorAgreementID
-- 9/25/07     KC        Used ID for Institution
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria
-- 10/10/07    KC        Fix for ProjectFundNumber
-- 10/18/07    KC        Fixed Searching on Dates with TimeComponent
-- 02/15/08    KC        Added Search on AI.ProjectFundNumber as well as BUDG.FundNumber
-- 12/17/08    KC        New Security function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
--
-- =======================================================================
CREATE PROCEDURE dbo.RptAgreeFileFolderLabels
     (    @StartDate                  VARCHAR(20) = NULL,
          @EndDate                    VARCHAR(20) = NULL,
          @DateType                   VARCHAR(1) = '1',     --1=Created,2=Deadline
          @Institution                VARCHAR(100) = NULL, 
          @FolderNumber               VARCHAR(12) = NULL,
          @FolderNumberEnd            VARCHAR(12) = NULL,
          @FundNumber                 VARCHAR(30) = NULL,
          @PrincipalInvestigator      VARCHAR(100) = NULL, 
          @StartOnLabel               VARCHAR(5) = '1',
          @DomainUserId               INT
     )
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT,
               @EmptyRows        INT,
               @StartDateDT      DATETIME,
               @EndDateDT        DATETIME

     -- Datetype & Others
     IF @StartDate IN ('ALL','','0',NULL) OR ISDATE(@StartDate) = 0 SET @StartDateDT = NULL 
               ELSE SET @StartDateDT = CONVERT(DATETIME,@StartDate)
     IF @EndDate IN ('ALL','','0',NULL) OR ISDATE(@EndDate) = 0 SET @EndDateDT = NULL 
               ELSE SET @EndDateDT = CONVERT(DATETIME,@EndDate)
     IF @DateType NOT IN ('1','2') OR (@DateType IS NULL AND @StartDateDT IS NOT NULL) SET @DateType = '1' 
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @FundNumber IN ('ALL','','0') SET @FundNumber = NULL
     IF @FolderNumber IN ('ALL','','0') SET @FolderNumber = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL

     -- FolderNumbers
     IF @FolderNumberEnd IS NULL OR @FolderNumberEnd = ''
          SELECT @FolderNumberEnd = @FolderNumber

     -- How many extra rows do we need to insert? 
     IF IsNumeric(@StartOnLabel) = 0 
          SET @EmptyRows = 0
     ELSE IF CONVERT(INT,@StartOnLabel) > 10 OR CONVERT(INT,@StartOnLabel) < 1
          SET @EmptyRows = 0
          ELSE
              SET @EmptyRows = CONVERT(INT,@StartOnLabel) - 1 
     
     CREATE TABLE #tt_EmptyRows
          (ProposalNumber          VARCHAR(1),
          FundNumber               VARCHAR(1),
          PrincipalInvestigator    VARCHAR(1),
          BudgetStartDate          VARCHAR(1),
          BudgetEndDate            VARCHAR(1),
          BudgetYear               VARCHAR(1),
          ProjectStartDate         VARCHAR(1),
          ProjectEndDate           VARCHAR(1),
          Sponsor                  VARCHAR(1),
          SponsorAgreementID       VARCHAR(1),
          DocNumber                VARCHAR(1),
          ReceivedDate             VARCHAR(1),
          Deadline                 VARCHAR(1),
          PropNumber               VARCHAR(1),
          PrincipalInvestigatorId  VARCHAR(1),
          AgrResourceId            VARCHAR(1),
          AgreementId              VARCHAR(1),
          EmptyRow                 INT)

     -- Insert blanks into table...          
     WHILE @EmptyRows >= 1
     BEGIN               
          INSERT #tt_EmptyRows (EmptyRow)
          VALUES (@EmptyRows)
          
          SET  @EmptyRows = @EmptyRows - 1     
     END
     
     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 

     -- Return Query Results
     SELECT    ProposalNumber,
               FundNumber,
               PrincipalInvestigator,
               BudgetStartDate,
               BudgetEndDate,
               BudgetYear,
               ProjectStartDate,
               ProjectEndDate,
               Sponsor,
               SponsorAgreementID,
               DocNumber,
               ReceivedDate,
               Deadline,
               PropNumber,
               PrincipalInvestigatorId,
               AgrResourceId,
               AgreementId,
               EmptyRow
     FROM      #tt_EmptyRows
     UNION     
     SELECT    AI.FolderNumber AS ProposalNumber,
               ISNULL(BUDG.FundNumber,AI.ProjectFundNumber) AS FundNumber,
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               CONVERT(VARCHAR(10),BUDG.BudgetStartDate,101) AS BudgetStartDate,
               CONVERT(VARCHAR(10),BUDG.BudgetEndDate,101) AS BudgetEndDate,
               BUDG.SequenceYear AS BudgetYear,
               CONVERT(VARCHAR(10),AI.ProjectStartDate,101) AS ProjectStartDate,
               CONVERT(VARCHAR(10),AI.ProjectEndDate,101) AS ProjectEndDate,
               AI.SponsorName AS Sponsor,
               AI.SponsorAgreementID AS SponsorAgreementID,
               AI.DocumentNumber AS DocNumber,
               AI.ReceivedDate AS ReceivedDate,
               AI.DeadlineDate AS Deadline,
               AI.InfoEdPropNumber AS PropNumber,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.ResourceId AS AgrResourceId,
               AI.AgreementId AS AgreementId,
               0 AS EmptyRow
     FROM      dbo.AgrAgreement_Info AI LEFT OUTER JOIN
               
               -- We need only the latest BudgetYear and BudgetDates for each 
               -- FolderNumber within the search date params.
               dbo.fnGetPeriodBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId INNER JOIN
               
               -- Security Issues: Not all Users can see all Agreements!
               #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId
                         
     WHERE     (@StartDateDT IS NULL OR ((@DateType = '1' AND dbo.fnRemoveTime(AI.ReceivedDate) BETWEEN  @StartDateDT AND @EndDateDT)
                OR (@DateType = '2' AND AI.DeadlineDate BETWEEN  @StartDateDT AND @EndDateDT))) AND
               (@FolderNumber IS NULL OR AI.FolderNumber BETWEEN  @FolderNumber AND @FolderNumberEnd)  AND
               (@FundNumber IS NULL OR BUDG.FundNumber = @FundNumber OR AI.ProjectFundNumber = @FundNumber) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator )))
     ORDER BY AI.ProposalNumber

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     BEGIN
          DROP TABLE #tt_EmptyRows
          DROP TABLE #tt_Agreements
          RETURN -1
     END
     
     DROP TABLE #tt_EmptyRows
     DROP TABLE #tt_Agreements
     
     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptAgreeFileFolderLabels') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptAgreeFileFolderLabels >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptAgreeFileFolderLabels >>>'
go

GRANT EXECUTE ON dbo.RptAgreeFileFolderLabels TO InsightReport,Insight
go